package br.com.as.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import br.com.as.dominio.Cliente;
import br.com.as.dominio.ItemVenda;
import br.com.as.dominio.Produto;
import br.com.as.dominio.Venda;
import br.com.as.utils.AsUtils;
import br.com.as.utils.HibernateUtility;

/**
 * Classe para consistencia de venda
 * 
 * @author msante
 *
 */
public class VendaDAO {

	@SuppressWarnings("unchecked")
	public  List<Venda> listar() throws Exception{
		List<Venda> lista = null;
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery(" from Venda ");		
			lista = query.list();
			
			for (Venda venda : lista) {
				Query query2 = sessao.createQuery(" from ItemVenda i where i.venda.id =:id ");
				query2.setLong("id", venda.getId());
				venda.setItensVenda(query2.list());
			}
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
		return lista;
	}

	public  void incluir(Venda venda) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {
			Query temp = sessao.createQuery(" select max(v.notaFiscal) from Venda v ");
			Long nfAnterior = (Long) temp.uniqueResult();
			if(nfAnterior == null){
				venda.setNotaFiscal(1L);
			} else {
				venda.setNotaFiscal(++nfAnterior);
			}

			tx = sessao.beginTransaction();
			sessao.saveOrUpdate(venda);
			for (ItemVenda item : venda.getItensVenda()) {
				item.setVenda(venda);
				sessao.saveOrUpdate(item);			
			}
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}
	
	public  Venda consultar(String notaFiscal) throws Exception{
		Venda venda = null;
		Session sessao = HibernateUtility.getSession();
		Transaction tx = null;
		try {
			tx = sessao.beginTransaction();
			Query query = sessao.createQuery("from Venda cli where cli.notaFiscal =:notaFiscal");		
			query.setString("notaFiscal", notaFiscal);
			venda = (Venda) query.uniqueResult();			
			tx.commit();			
		} catch (Exception e) {			
			tx.rollback();
			throw e;
		} finally {
			sessao.close();
		}
		return venda;		
	}
	
	@SuppressWarnings("unchecked")
	public List<Venda> consultar(Cliente cliente) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery("from Venda cli where cli.cliente.id =:id");		
			query.setLong("id", cliente.getId());
			return query.list();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<ItemVenda> consultar(Produto produto) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery("from ItemVenda cli where cli.produto.id =:id");		
			query.setLong("id", produto.getId());
			return query.list();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<Object> clienteVip(Date dataFiltro) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			StringBuffer str = new StringBuffer();
			str.append(" select v.idcliente, sum(v.precoTotal) as total from agilesystem.venda v");
			str.append(" where v.data between date");
			str.append(" '"+new SimpleDateFormat("yyyy-MM-dd").format(AsUtils.primeiroDiaDoMes(dataFiltro, true))+"'");
			str.append(" and date");
			str.append(" '"+new SimpleDateFormat("yyyy-MM-dd").format(AsUtils.ultimoDiaDoMes(dataFiltro, true))+"'");
			str.append(" group by v.idcliente");
			str.append(" order by total desc");

			Query query = sessao.createSQLQuery(str.toString());
			
			query.setMaxResults(10);
			return query.list();
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}
	public void alterar(Venda venda, List<ItemVenda> itensExcluir, List<ItemVenda> itensAlterados, List<ItemVenda> itensNovos) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {			
			tx = sessao.beginTransaction();
			sessao.saveOrUpdate(venda.getCliente());
			
			for (ItemVenda it : itensNovos) {
				sessao.save(it);
			}
			
			for (ItemVenda it : itensAlterados) {
				sessao.update(it);
			}
			
			for (ItemVenda it : itensExcluir) {
				sessao.delete(it);
			}
			
			sessao.update(venda);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}

	public void excluir(Venda venda) throws Exception{
		Transaction tx = null;
		Session sessao = HibernateUtility.getSession();
		try {			
			tx = sessao.beginTransaction();
			for (ItemVenda it : venda.getItensVenda()) {
				sessao.delete(it);
			}
			sessao.delete(venda);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			sessao.close();
			throw e;
		}finally{
			sessao.close();	
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<ItemVenda> consultar(Venda venda) throws Exception{
		Session sessao = HibernateUtility.getSession();
		try {
			Query query = sessao.createQuery("from ItemVenda cli where cli.venda.id =:id");		
			query.setLong("id", venda.getId());
			return query.list();			
		} catch (Exception e) {			
			throw e;
		} finally {
			sessao.close();
		}
	}

}
